
<!DOCTYPE html
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<!DOCTYPE html><html xmlns="http://www.w3.org/1999/xhtml">
   <head>
      <meta charset="utf-8"></meta>
      <meta name="viewport" content="width=device-width, initial-scale=1.0"></meta>
      <title>7.3.&nbsp;Load CSV - Chapter&nbsp;7.&nbsp;Export / Import</title>
      <link rel="stylesheet" type="text/css" href="../../docbook.css"></link>
      <link rel="stylesheet" type="text/css" href="//maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css"></link>
      <link rel="stylesheet" type="text/css" href="//cdn.datatables.net/1.10.13/css/jquery.dataTables.min.css"></link>
      <link rel="stylesheet" type="text/css" href="//cdnjs.cloudflare.com/ajax/libs/font-awesome/4.1.0/css/font-awesome.min.css"></link>
      <link rel="stylesheet" type="text/css" href="//fonts.googleapis.com/css?family=Open+Sans:400,300,400italic,600,300italic"></link>
      <link rel="stylesheet" type="text/css" href="//cdnjs.cloudflare.com/ajax/libs/codemirror/5.11.0/codemirror.min.css"></link>
      <link rel="stylesheet" type="text/css" href="//cdnjs.cloudflare.com/ajax/libs/codemirror/5.11.0/theme/neo.min.css"></link>
      <link rel="stylesheet" type="text/css" href="../../css/chunked-base.css"></link>
      <link rel="stylesheet" type="text/css" href="../../css/extra.css"></link><script src="//code.jquery.com/jquery-1.12.4.js" type="text/javascript"></script><script src="//cdn.datatables.net/1.10.13/js/jquery.dataTables.min.js" type="text/javascript"></script><script src="//maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js" type="text/javascript"></script><script src="//cdnjs.cloudflare.com/ajax/libs/codemirror/5.11.0/codemirror.min.js" type="text/javascript"></script><script src="//cdnjs.cloudflare.com/ajax/libs/codemirror/5.11.0/addon/runmode/runmode.min.js" type="text/javascript"></script><script src="//cdnjs.cloudflare.com/ajax/libs/codemirror/5.11.0/mode/cypher/cypher.min.js" type="text/javascript"></script><script src="../../javascript/datatable.js" type="text/javascript"></script><script src="../../javascript/colorize.js" type="text/javascript"></script><script src="../../javascript/tabs-for-chunked.js" type="text/javascript"></script><script src="../../javascript/mp-nav.js" type="text/javascript"></script><script src="../../javascript/versionswitcher.js" type="text/javascript"></script><script src="../../javascript/version.js" type="text/javascript"></script><script src="//s3-eu-west-1.amazonaws.com/alpha.neohq.net/docs/new-manual/assets/search.js" type="text/javascript"></script><meta name="generator" content="DocBook XSL Stylesheets V1.79.1"></meta>
      <link rel="prev" href="../load-json/" title="7.2.&nbsp;Load JSON"></link>
      <link rel="next" href="../import-csv/" title="7.4.&nbsp;Import CSV"></link>
      <link rel="shortcut icon" href="https://neo4j.com/wp-content/themes/neo4jweb/favicon.ico"></link><script>
        $(document).ready(function() {
          CodeMirror.colorize();
          tabTheSource($('body'));
          var $header = $('header').first();
          $header.prepend(
            $('<a href="" id="logo"><img src="https://neo4j.com/wp-content/themes/neo4jweb/assets/images/neo4j-logo-2015.png" alt="Neo4j Logo"></img></a>')
          );
          var $sidebar = $('<div id="sidebar-wrapper"></div>');
          $.get('toc.html', function (d){
            $(d).appendTo($sidebar);
            highlightToc();
            highlightLibraryHeader();
          });
          $sidebar.insertAfter($('header').first());
        });
        </script></head>
   <body>
      <header>
         <div class="searchbox">
            <form id="search-form" class="search" name="search-form" role="search"><input id="search-form-input" name="q" title="search" type="search" lang="en" placeholder="Search Neo4j docs..." aria-label="Search Neo4j documentation" max-length="128" required="required"></input><input id="search-form-button" type="submit" value="Search"></input></form>
         </div>
         <ul class="documentation-library">
            <li><a href="https://neo4j.com/docs/operations-manual/current">Operations Manual</a></li>
            <li><a href="https://neo4j.com/docs/developer-manual/current/">Developer Manual</a></li>
            <li><a href="https://neo4j.com/docs/ogm-manual/current/">OGM Manual</a></li>
            <li><a href="https://neo4j.com/docs/graph-algorithms/current/">Graph Algorithms</a></li>
            <li><a href="https://neo4j-contrib.github.io/neo4j-apoc-procedures/3.4/">APOC</a></li>
            <li><a href="https://neo4j.com/docs/java-reference/current/">Java Reference</a></li>
         </ul>
         <nav id="header-nav"><span class="nav-previous"><a accesskey="p" href="../load-json/"><span class="fa fa-long-arrow-left" aria-hidden="true"></span>Load JSON</a></span><span class="nav-current">
               <p class="nav-title hidden">7.3.&nbsp;Load CSV</p></span><span class="nav-next"><a accesskey="n" href="../import-csv/">Import CSV<span class="fa fa-long-arrow-right" aria-hidden="true"></span></a></span></nav>
      </header>
      <div id="search-results" class="hidden"></div>
      <section class="section" id="load-csv">
         <div class="titlepage">
            <div>
               <div>
                  <h2 class="title" style="clear: both"><a class="anchor" href="#load-csv"></a>7.3.&nbsp;Load CSV
                  </h2>
               </div>
            </div>
         </div>
         <p>Many existing applications and data integrations use CSV as the minimal denominator format.</p>
         <p>In Cypher it is supported by <code class="literal">LOAD CSV</code> and with the <code class="literal">neo4j-import</code> (<code class="literal">neo4j-admin import</code>) for bulk imports.
         </p>
         <p>Usually a CSV file is text with delimiters (most often comma, but also tab (TSV) and colon (DSV)) separating columns and newlines
            for rows.
            Fields are possibly quoted to handle stray quotes, newlines, and the use of the delimeter within a field.
         </p>
         <p>The existing <code class="literal">LOAD CSV</code> works ok for most uses, but there were a few features missing, that <code class="literal">apoc.load.csv</code> and <code class="literal">apoc.load.xls</code> add.
         </p>
         <div class="itemizedlist">
            <ul class="itemizedlist" style="list-style-type: disc; ">
               <li class="listitem">provide a line number</li>
               <li class="listitem">provide both a map and a list representation of each line</li>
               <li class="listitem">automatic data conversion (including split into arrays)</li>
               <li class="listitem">option to keep the original string formatted values</li>
               <li class="listitem">ignoring fields (makes it easier to assign a full line as properties)</li>
               <li class="listitem">headerless files</li>
               <li class="listitem">replacing certain values with null</li>
            </ul>
         </div>
         <p>The apoc procedures also support reading compressed files.</p>
         <p>The data conversion is useful for setting properties directly, but for computation within Cypher it&#8217;s problematic as Cypher
            doesn&#8217;t know the type of map values so they default to <code class="literal">Any</code>.
         </p>
         <p>To use them correctly, you&#8217;ll have to indicate their type to Cypher by using the built-in (e.g. <code class="literal">toInteger</code>) or apoc (e.g. <code class="literal">apoc.convert.toBoolean</code>) conversion functions on the value.
         </p>
         <p>For reading from files you&#8217;ll have to enable the config option:</p><pre class="screen highlight"><code>apoc.import.file.enabled=true</code></pre><p>By default file paths are global, for paths relative to the <code class="literal">import</code> directory set:
         </p><pre class="screen highlight"><code>apoc.import.file.use_neo4j_config=true</code></pre><section class="section" id="_examples_for_apoc_load_csv">
            <div class="titlepage">
               <div>
                  <div>
                     <h3 class="title"><a class="anchor" href="#_examples_for_apoc_load_csv"></a>7.3.1.&nbsp;Examples for apoc.load.csv
                     </h3>
                  </div>
               </div>
            </div>
            <p><span class="formalpara-title">test.csv.&nbsp;</span>
               
            </p><pre class="screen highlight"><code>name,age,beverage
Selma,9,Soda
Rana,12,Tea;Milk
Selina,19,Cola</code></pre><p>
               
            </p><pre class="screen highlight"><code>CALL apoc.load.csv('/tmp/test.csv') yield lineNo, map, list
RETURN *;</code></pre><pre class="screen highlight"><code>+---------------------------------------------------------------------------------------+
| lineNo | list                       | map                                             |
+---------------------------------------------------------------------------------------+
| 0      | ["Selma", "9", "Soda"]     | {name: "Selma", age: "9", beverage: "Soda"}     |
| 1      | ["Rana", "12", "Tea;Milk"] | {name: "Rana", age: "12", beverage: "Tea;Milk"} |
| 2      | ["Selina", "19", "Cola"]   | {name: "Selina", age: "19", beverage: "Cola"}   |
+---------------------------------------------------------------------------------------+</code></pre></section>
         <section class="section" id="_configuration_options">
            <div class="titlepage">
               <div>
                  <div>
                     <h3 class="title"><a class="anchor" href="#_configuration_options"></a>7.3.2.&nbsp;Configuration Options
                     </h3>
                  </div>
               </div>
            </div>
            <p>Besides the file you can pass in a config map:</p>
            <div class="informaltable">
               <div class="table" id="d0e6364">
                  <table class="informaltable" border="1">
                     <colgroup>
                        <col class="col_1"></col>
                        <col class="col_2"></col>
                        <col class="col_3"></col>
                     </colgroup>
                     <thead>
                        <tr>
                           <th style="text-align: left; vertical-align: top; ">name</th>
                           <th style="text-align: left; vertical-align: top; ">default</th>
                           <th style="text-align: left; vertical-align: top; ">description</th>
                        </tr>
                     </thead>
                     <tbody>
                        <tr>
                           <td style="text-align: left; vertical-align: top; ">
                              <p><code class="literal">skip</code></p>
                           </td>
                           <td style="text-align: left; vertical-align: top; ">
                              <p><code class="literal">none</code></p>
                           </td>
                           <td style="text-align: left; vertical-align: top; ">
                              <p>skip result rows</p>
                           </td>
                        </tr>
                        <tr>
                           <td style="text-align: left; vertical-align: top; ">
                              <p><code class="literal">limit</code></p>
                           </td>
                           <td style="text-align: left; vertical-align: top; ">
                              <p><code class="literal">none</code></p>
                           </td>
                           <td style="text-align: left; vertical-align: top; ">
                              <p>limit result rows</p>
                           </td>
                        </tr>
                        <tr>
                           <td style="text-align: left; vertical-align: top; ">
                              <p><code class="literal">header</code></p>
                           </td>
                           <td style="text-align: left; vertical-align: top; ">
                              <p><code class="literal">true</code></p>
                           </td>
                           <td style="text-align: left; vertical-align: top; ">
                              <p>indicates if file has a header</p>
                           </td>
                        </tr>
                        <tr>
                           <td style="text-align: left; vertical-align: top; ">
                              <p><code class="literal">sep</code></p>
                           </td>
                           <td style="text-align: left; vertical-align: top; ">
                              <p><code class="literal">','</code></p>
                           </td>
                           <td style="text-align: left; vertical-align: top; ">
                              <p>separator character or 'TAB'</p>
                           </td>
                        </tr>
                        <tr>
                           <td style="text-align: left; vertical-align: top; ">
                              <p><code class="literal">quoteChar</code></p>
                           </td>
                           <td style="text-align: left; vertical-align: top; ">
                              <p><code class="literal">'"'</code></p>
                           </td>
                           <td style="text-align: left; vertical-align: top; ">
                              <p>the char to use for quoted elements</p>
                           </td>
                        </tr>
                        <tr>
                           <td style="text-align: left; vertical-align: top; ">
                              <p><code class="literal">arraySep</code></p>
                           </td>
                           <td style="text-align: left; vertical-align: top; ">
                              <p><code class="literal">';'</code></p>
                           </td>
                           <td style="text-align: left; vertical-align: top; ">
                              <p>array separator</p>
                           </td>
                        </tr>
                        <tr>
                           <td style="text-align: left; vertical-align: top; ">
                              <p><code class="literal">ignore</code></p>
                           </td>
                           <td style="text-align: left; vertical-align: top; ">
                              <p><code class="literal">[]</code></p>
                           </td>
                           <td style="text-align: left; vertical-align: top; ">
                              <p>which columns to ignore</p>
                           </td>
                        </tr>
                        <tr>
                           <td style="text-align: left; vertical-align: top; ">
                              <p><code class="literal">nullValues</code></p>
                           </td>
                           <td style="text-align: left; vertical-align: top; ">
                              <p><code class="literal">[]</code></p>
                           </td>
                           <td style="text-align: left; vertical-align: top; ">
                              <p>which values to treat as null, e.g. <code class="literal">['na',false]</code></p>
                           </td>
                        </tr>
                        <tr>
                           <td style="text-align: left; vertical-align: top; ">
                              <p><code class="literal">mapping</code></p>
                           </td>
                           <td style="text-align: left; vertical-align: top; ">
                              <p><code class="literal">{}</code></p>
                           </td>
                           <td style="text-align: left; vertical-align: top; ">
                              <p>per field mapping, entry key is field name, .e.g <code class="literal">{years:{&#8230;&#8203;.}</code> see below
                              </p>
                           </td>
                        </tr>
                     </tbody>
                  </table>
               </div>
            </div>
            <div class="table" id="d0e6491">
               <table class="table" summary="mapping config for each field in the mapping entry" border="1">
                  <caption class="table-title">Table&nbsp;7.2.&nbsp;mapping config for each field in the <code class="literal">mapping</code> entry
                  </caption>
                  <colgroup>
                     <col class="col_1"></col>
                     <col class="col_2"></col>
                     <col class="col_3"></col>
                  </colgroup>
                  <thead>
                     <tr>
                        <th style="text-align: left; vertical-align: top; ">name</th>
                        <th style="text-align: left; vertical-align: top; ">default</th>
                        <th style="text-align: left; vertical-align: top; ">description</th>
                     </tr>
                  </thead>
                  <tbody>
                     <tr>
                        <td style="text-align: left; vertical-align: top; ">
                           <p><code class="literal">type</code></p>
                        </td>
                        <td style="text-align: left; vertical-align: top; ">
                           <p><code class="literal">none</code></p>
                        </td>
                        <td style="text-align: left; vertical-align: top; ">
                           <p>'int', 'string' etc.</p>
                        </td>
                     </tr>
                     <tr>
                        <td style="text-align: left; vertical-align: top; ">
                           <p><code class="literal">array</code></p>
                        </td>
                        <td style="text-align: left; vertical-align: top; ">
                           <p><code class="literal">false</code></p>
                        </td>
                        <td style="text-align: left; vertical-align: top; ">
                           <p>indicates if field is an array</p>
                        </td>
                     </tr>
                     <tr>
                        <td style="text-align: left; vertical-align: top; ">
                           <p><code class="literal">arraySep</code></p>
                        </td>
                        <td style="text-align: left; vertical-align: top; ">
                           <p><code class="literal">';'</code></p>
                        </td>
                        <td style="text-align: left; vertical-align: top; ">
                           <p>separator for array</p>
                        </td>
                     </tr>
                     <tr>
                        <td style="text-align: left; vertical-align: top; ">
                           <p><code class="literal">name</code></p>
                        </td>
                        <td style="text-align: left; vertical-align: top; ">
                           <p><code class="literal">none</code></p>
                        </td>
                        <td style="text-align: left; vertical-align: top; ">
                           <p>rename field</p>
                        </td>
                     </tr>
                     <tr>
                        <td style="text-align: left; vertical-align: top; ">
                           <p><code class="literal">ignore</code></p>
                        </td>
                        <td style="text-align: left; vertical-align: top; ">
                           <p><code class="literal">false</code></p>
                        </td>
                        <td style="text-align: left; vertical-align: top; ">
                           <p>ignore/remove this field</p>
                        </td>
                     </tr>
                     <tr>
                        <td style="text-align: left; vertical-align: top; ">
                           <p><code class="literal">nullValues</code></p>
                        </td>
                        <td style="text-align: left; vertical-align: top; ">
                           <p><code class="literal">[]</code></p>
                        </td>
                        <td style="text-align: left; vertical-align: top; ">
                           <p>which values to treat as null, e.g. <code class="literal">['na',false]</code></p>
                        </td>
                     </tr>
                  </tbody>
               </table>
            </div><pre class="screen highlight"><code>CALL apoc.load.csv('/tmp/test.csv',
  {skip:1,limit:1,header:true,ignore:'name',
   mapping:{age:{type:'int'},beverage:{array:true,arraySep:';',name:'drinks'}) yield lineNo, map, list
RETURN *;</code></pre><pre class="screen highlight"><code>+---------------------------------------------------------------------------------------+
| lineNo | list                       | map                                             |
+---------------------------------------------------------------------------------------+
| 0      | ["Selma", "9", "Soda"]     | {name: "Selma", age: "9", beverage: "Soda"}     |
| 1      | ["Rana", "12", "Tea;Milk"] | {name: "Rana", age: "12", beverage: "Tea;Milk"} |
| 2      | ["Selina", "19", "Cola"]   | {name: "Selina", age: "19", beverage: "Cola"}   |
+---------------------------------------------------------------------------------------+</code></pre></section>
         <section class="section" id="_transaction_batching">
            <div class="titlepage">
               <div>
                  <div>
                     <h3 class="title"><a class="anchor" href="#_transaction_batching"></a>7.3.3.&nbsp;Transaction Batching
                     </h3>
                  </div>
               </div>
            </div>
            <p>To handle large files, <code class="literal">USING PERIODIC COMMIT</code> can be prepended to <code class="literal">LOAD CSV</code>, you&#8217;ll have to watch  out though for <span class="strong"><strong>Eager</strong></span> operations which might break that behavior.
            </p>
            <p>In apoc you can combine any data source with <code class="literal">apoc.periodic.iterate</code> to achieve the same.
            </p><pre class="programlisting highlight"><code data-lang="cypher">CALL apoc.periodic.iterate('
CALL apoc.load.csv({url}) yield map as row return row
','
CREATE (p:Person) SET p = row
', {batchSize:10000, iterateList:true, parallel:true});</code></pre><div class="admonitionblock note">
               <table>
                  <tbody>
                     <tr>
                        <td class="icon"><i class="fa icon-note" title="note"></i></td>
                        <td class="content">
                           <p>Please note that the parallel operation only works well for non-conflicting updates otherwise you might run into deadlocks.</p>
                        </td>
                     </tr>
                  </tbody>
               </table>
            </div>
            <p>To make these datastructures available to Cypher, you can use <code class="literal">apoc.load.xml</code>.
               It takes a file or http URL and parses the XML into a map datastructure.
            </p>
            <div class="admonitionblock note">
               <table>
                  <tbody>
                     <tr>
                        <td class="icon"><i class="fa icon-note" title="note"></i></td>
                        <td class="content">
                           <p>in previous releases we&#8217;ve had <code class="literal">apoc.load.xmlSimple</code>. This is now deprecated and got superseeded by
                              <code class="literal">apoc.load.xml(url, [xPath], [config], true)</code>.Simple XML Format
                           </p>
                        </td>
                     </tr>
                  </tbody>
               </table>
            </div>
            <p>See the following usage-examples for the procedures.</p>
         </section>
      </section>
      <footer><script type="text/javascript">
          (function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
            (i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
          m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
          })(window,document,'script','//www.google-analytics.com/analytics.js','ga');
          //Allow Linker
          ga('create', 'UA-1192232-34','auto', {'allowLinker': true});
          ga('send', 'pageview');
          // Load the plugin.
          ga('require', 'linker');
          // Define which domains to autoLink.
          ga('linker:autoLink', ['neo4j.org','neo4j.com','neotechnology.com','graphdatabases.com','graphconnect.com']);
        </script><script type="text/javascript">
          document.write(unescape("%3Cscript src='//munchkin.marketo.net/munchkin.js' type='text/javascript'%3E%3C/script%3E"));
        </script><script>Munchkin.init('773-GON-065');</script></footer>
   </body>
</html>